//
//  HistoryDataBase.m
//  SIPphone
//
//  Created by wrt on 13-12-16.
//  Copyright (c) 2013年 wrtsoft. All rights reserved.
//

#import "HistoryDataBase.h"


//数据库对象的单例
static HistoryDataBase *shareHistoryDataBase = nil;

@implementation HistoryDataBase

//单例的初始化方法
+ (HistoryDataBase *)shareHistoryDataBase
{
    @synchronized(self)
    {
        if (shareHistoryDataBase == nil)
        {
            shareHistoryDataBase = [[self alloc] init];
        }
    }
    return shareHistoryDataBase;
}

//切换表名
- (void)changeTableName:(NSString *)userName
{
    self.tableNameForUser = [[NSString alloc] init];
    self.tableNameForUser = [NSString stringWithFormat:@"historyTable_%@",userName];
}

//创建数据库
- (void)createDb
{
    
    
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //创建表
        char *error;
        NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(%@ TEXT,%@ TEXT,%@ TEXT, %@ TEXT)",self.tableNameForUser,CALL_TIME,CALL_NUMBER,CALL_STATUS,CALL_DISPLAYNAME];
        //运行SQL语句
        if(sqlite3_exec(db, [createSQL UTF8String], NULL, NULL, &error) !=SQLITE_OK
           )
        {
            NSLog(@"建表失败！%s",error);
        }
        //关闭数据库
        sqlite3_close(db);
    }
}
//未接形式存数据库
- (void)callincomingNotAnswer
{
    //数据库写操作
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@) VALUES(?,?,?,?)",self.tableNameForUser,CALL_TIME,CALL_NUMBER,CALL_STATUS,CALL_DISPLAYNAME];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            NSString *tempIncomingTime = [SIPInterface shareSIPInterface].incomingTime;
            NSString *tempIncomingNumber = [SIPInterface shareSIPInterface].incomingNumber;
            NSString *tempIncomingDisplayName = [SIPInterface shareSIPInterface].incomingDisplayName;
            //绑定参数
            sqlite3_bind_text(statment, 1, [tempIncomingTime UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 2, [tempIncomingNumber UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 3, [@"未接" UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 4, [tempIncomingDisplayName UTF8String], -1, NULL);
            //执行插入
            if(sqlite3_step(statment) !=SQLITE_DONE)
            {
                NSLog(@"插入失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        
        //关闭数据库
        sqlite3_close(db);
    }
}
//已接形式存数据库
- (void)callincomingAnswer
{
    //数据库写操作
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@) VALUES(?,?,?,?)",self.tableNameForUser,CALL_TIME,CALL_NUMBER,CALL_STATUS,CALL_DISPLAYNAME];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            NSString *tempIncomingTime = [SIPInterface shareSIPInterface].incomingTime;
            NSString *tempIncomingNumber = [SIPInterface shareSIPInterface].incomingNumber;
            NSString *tempIncomingDisplayName = [SIPInterface shareSIPInterface].incomingDisplayName;
            //绑定参数
            sqlite3_bind_text(statment, 1, [tempIncomingTime UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 2, [tempIncomingNumber UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 3, [@"来电" UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 4, [tempIncomingDisplayName UTF8String], -1, NULL);
            //执行插入
            if(sqlite3_step(statment) !=SQLITE_DONE)
            {
                NSLog(@"插入失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        
        //关闭数据库
        sqlite3_close(db);
    }
}

//写进数据库的操作
- (void)writeIntoDatabaseWithNumber:(NSString *)number andDisplayName:(NSString *)displayName
{
    NSString *dateAndTime;
    NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
    [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    dateAndTime = [dateFormatter stringFromDate:[NSDate date]];
    
    //数据库写操作
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK)
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //INSERT OR REPLACE INTO tablename(text,text,...,integer) VALUES(?,?,?,?,?,?)
        NSString *sqlStr = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@(%@,%@,%@,%@) VALUES(?,?,?,?)",self.tableNameForUser,CALL_TIME,CALL_NUMBER,CALL_STATUS,CALL_DISPLAYNAME];
        sqlite3_stmt *statment;
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statment, NULL) == SQLITE_OK)
        {
            //绑定参数
            sqlite3_bind_text(statment, 1, [dateAndTime UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 2, [number UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 3, [@"拨出" UTF8String], -1, NULL);
            sqlite3_bind_text(statment, 4, [displayName UTF8String], -1, NULL);
            //执行插入
            if(sqlite3_step(statment) !=SQLITE_DONE)
            {
                NSLog(@"插入失败");
            }
        }
        
        //释放状态资源
        sqlite3_finalize(statment);
        
        //关闭数据库
        sqlite3_close(db);
    }
}
//读取数据库，并将其存入对应数组中
- (void)loadArray:(int)a
{
    
    //读取出来的时间数组
    self.timeArray = [[NSMutableArray alloc] init];
    //读取出来的号码数组
    self.numberArray = [[NSMutableArray alloc] init];
    //读取出来的状态数组
    self.statusArray = [[NSMutableArray alloc] init];
    //读取出来的昵称数组
    self.displayNameArray = [[NSMutableArray alloc] init];
    
    //数据库先执行一遍读
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        NSString *sqlStr = [NSString stringWithFormat:@"SELECT %@,%@,%@,%@ FROM %@ ORDER BY %@ desc",CALL_TIME,CALL_NUMBER,CALL_STATUS,CALL_DISPLAYNAME,self.tableNameForUser,CALL_TIME];
        
        sqlite3_stmt *statement;
        
        //预处理
        if(sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            //执行
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                if (a == 0)
                {
                    char *text1 = (char *)sqlite3_column_text(statement, 0);
                    if (text1 == nil)
                    {
                        text1 = "未知";
                    }
                    self.tempTime = [[NSString alloc] initWithUTF8String:text1];
                    
                    
                    char *text2 = (char *)sqlite3_column_text(statement, 1);
                    if (text2 == nil)
                    {
                        text2 = "未知";
                    }
                    self.tempNumber = [[NSString alloc] initWithUTF8String:text2];
                    
                    char *text3 = (char *)sqlite3_column_text(statement, 2);
                    self.tempStatus = [[NSString alloc] initWithUTF8String:text3];
                    
                    char *text4 = (char *)sqlite3_column_text(statement, 3);
                    if (text4 == nil)
                    {
                        text4 = "未知";
                    }
                    self.tempDisplayName = [[NSString alloc] initWithUTF8String:text4];
                    if (! (self.tempDisplayName.length > 0))
                    {
                        self.tempDisplayName = self.tempNumber;
                    }
                    
                    
                    if (self.tempTime.length > 0 && self.tempNumber.length > 0 && self.tempStatus > 0 && self.tempDisplayName.length > 0)
                    {
                        [self.timeArray addObject:self.tempTime];
                        [self.numberArray addObject:self.tempNumber];
                        [self.statusArray addObject:self.tempStatus];
                        [self.displayNameArray addObject:self.tempDisplayName];
                    }
                }
                else if (a == 1)
                {
                    char *text1 = (char *)sqlite3_column_text(statement, 0);
                    if (text1 == nil)
                    {
                        text1 = "未知";
                    }
                    self.tempTime = [[NSString alloc] initWithUTF8String:text1];
                    
                    char *text2 = (char *)sqlite3_column_text(statement, 1);
                    if (text2 == nil)
                    {
                        text2 = "未知";
                    }
                    self.tempNumber = [[NSString alloc] initWithUTF8String:text2];
                    
                    char *text3 = (char *)sqlite3_column_text(statement, 2);
                    self.tempStatus = [[NSString alloc] initWithUTF8String:text3];
                    
                    char *text4 = (char *)sqlite3_column_text(statement, 3);
                    if (text4 == nil)
                    {
                        text4 = "未知";
                    }
                    self.tempDisplayName = [[NSString alloc] initWithUTF8String:text4];
                    if (! (self.tempDisplayName.length > 0))
                    {
                        self.tempDisplayName = self.tempNumber;
                    }
                    if (self.tempTime.length > 0 && self.tempNumber.length > 0 && self.tempStatus > 0 && self.tempDisplayName.length > 0)
                    {
                        if ([self.tempStatus isEqualToString:@"拨出"])
                        {
                            [self.timeArray addObject:self.tempTime];
                            [self.numberArray addObject:self.tempNumber];
                            [self.statusArray addObject:self.tempStatus];
                            [self.displayNameArray addObject:self.tempDisplayName];
                        }
                    }
                }
                else if (a == 2)
                {
                    char *text1 = (char *)sqlite3_column_text(statement, 0);
                    if (text1 == nil)
                    {
                        text1 = "未知";
                    }
                    self.tempTime = [[NSString alloc] initWithUTF8String:text1];
                    
                    char *text2 = (char *)sqlite3_column_text(statement, 1);
                    if (text2 == nil)
                    {
                        text2 = "未知";
                    }
                    self.tempNumber = [[NSString alloc] initWithUTF8String:text2];
                    
                    char *text3 = (char *)sqlite3_column_text(statement, 2);
                    self.tempStatus = [[NSString alloc] initWithUTF8String:text3];
                    char *text4 = (char *)sqlite3_column_text(statement, 3);
                    if (text4 == nil)
                    {
                        text4 = "未知";
                    }
                    self.tempDisplayName = [[NSString alloc] initWithUTF8String:text4];
                    if (! (self.tempDisplayName.length > 0))
                    {
                        self.tempDisplayName = self.tempNumber;
                    }
                    if (self.tempTime.length > 0 && self.tempNumber.length > 0 && self.tempStatus > 0 && self.tempDisplayName.length > 0)
                    {
                        if ([self.tempStatus isEqualToString:@"来电"])
                        {
                            [self.timeArray addObject:self.tempTime];
                            [self.numberArray addObject:self.tempNumber];
                            [self.statusArray addObject:self.tempStatus];
                            [self.displayNameArray addObject:self.tempDisplayName];
                        }
                    }
                }
                else if (a == 3)
                {
                    char *text1 = (char *)sqlite3_column_text(statement, 0);
                    if (text1 == nil)
                    {
                        text1 = "未知";
                    }
                    self.tempTime = [[NSString alloc] initWithUTF8String:text1];
                    
                    char *text2 = (char *)sqlite3_column_text(statement, 1);
                    if (text2 == nil)
                    {
                        text2 = "未知";
                    }
                    self.tempNumber = [[NSString alloc] initWithUTF8String:text2];
                    
                    char *text3 = (char *)sqlite3_column_text(statement, 2);
                    self.tempStatus = [[NSString alloc] initWithUTF8String:text3];
                    char *text4 = (char *)sqlite3_column_text(statement, 3);
                    if (text4 == nil)
                    {
                        text4 = "未知";
                    }
                    self.tempDisplayName = [[NSString alloc] initWithUTF8String:text4];
                    if (! (self.tempDisplayName.length > 0))
                    {
                        self.tempDisplayName = self.tempNumber;
                    }
                    if (self.tempTime.length > 0 && self.tempNumber.length > 0 && self.tempStatus > 0 && self.tempDisplayName.length > 0)
                    {
                        if ([self.tempStatus isEqualToString:@"未接"])
                        {
                            [self.timeArray addObject:self.tempTime];
                            [self.numberArray addObject:self.tempNumber];
                            [self.statusArray addObject:self.tempStatus];
                            [self.displayNameArray addObject:self.tempDisplayName];
                        }
                    }
                }
            }
        }
        //释放状态资源
        sqlite3_finalize(statement);
        //关闭数据库
        sqlite3_close(db);
    }
}
//删除数据库全部内容
- (void)removeAlldata
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE 1 > 0",self.tableNameForUser];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
    
    [self.timeArray removeAllObjects];
    [self.numberArray removeAllObjects];
    [self.statusArray removeAllObjects];
    [self.displayNameArray removeAllObjects];
}
//删除单个数据
- (void)deleteWithTime:(NSString *)time
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = '%@'",self.tableNameForUser,CALL_TIME,time];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}

//删除没有时间数据和状态的数据
- (void)deleteNULLDataWithTime
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ is NULL",self.tableNameForUser,CALL_TIME];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}

//删除超出指定条数据的旧数据(按时间排序)
- (void)deleteExceedData:(int)i
{
    //数据库执行删除
    NSArray *myPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *myDocPath = myPaths[0];
    NSString *filename = [myDocPath stringByAppendingPathComponent:@"callhistory.db"];
    
    //打开数据库
    if(sqlite3_open([filename UTF8String], &db) != SQLITE_OK )
    {
        //关闭数据库
        sqlite3_close(db);
    }
    else
    {
        //执行删除
        NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE (SELECT COUNT(%@) FROM %@) > %d AND %@ IN (SELECT %@ FROM %@ ORDER BY %@ DESC LIMIT (SELECT COUNT(%@) FROM %@) OFFSET %d)",self.tableNameForUser,CALL_TIME,self.tableNameForUser,i,CALL_TIME,CALL_TIME,self.tableNameForUser,CALL_TIME,CALL_TIME,self.tableNameForUser,i];
        char *errorMsg;
        if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK)
        {
            NSLog(@"OK");
        }
        //关闭数据库
        sqlite3_close(db);
    }
}


@end
